﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Site.Components;
using TransportManager.UI.Pages;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using System.IO;

namespace Site.Report
{
    public partial class productinoutmonthreport : BasePage
    {
        HSSFWorkbook hssfworkbook;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                drpYear.GenerateYear(DateTime.Now.Year);
                drpMonth.GenerateMonth(DateTime.Now.Month);
            }
        }

        void BindData()
        {
            var ds = SqlHelper.ExecuteDataset(Session["Conn"].ToString(),
                "Proc_ProductInOutMonthReport", CurrentDateTime);
            gv.DataSource = ds;
            gv.DataBind();
        }

        public DateTime CurrentDateTime
        {
            get
            {
                return DateTime.Parse(drpYear.SelectedValue + "-" + drpMonth.SelectedValue + "-1");
            }
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            BindData();
        }

        protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
            }
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            string filename = string.Format("材料进出存{0}年{1}月报表.xls", drpYear.SelectedValue, drpMonth.SelectedValue);
            Response.ContentType = "application/vnd.ms-excel";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8)));
            Response.Clear();

            InitializeWorkbook();
            GenerateData();
            Response.BinaryWrite(WriteToStream().GetBuffer());
            Response.End();
        }


        void GenerateData()
        {

            var sheet1 = hssfworkbook.CreateSheet("Sheet1");

            sheet1.SetColumnWidth(1, 75 * 256);

            var row = sheet1.CreateRow(0);
            row.HeightInPoints = 40;
            var cell = row.CreateCell(0);
            cell.SetCellType(CellType.STRING);
            cell.SetCellValue("材料进出存月报表");
            var style = hssfworkbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            Font font = hssfworkbook.CreateFont();
            font.FontHeight = 20 * 20;

            style.SetFont(font);
            cell.CellStyle = style;
            var titlerangeaddress = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 6);
            sheet1.AddMergedRegion(titlerangeaddress);




            var rowtime = sheet1.CreateRow(1);
            rowtime.HeightInPoints = 30;
            var celltime = rowtime.CreateCell(0);
            celltime.SetCellType(CellType.STRING);
            celltime.SetCellValue(string.Format("{0}  年 {1}  月", drpYear.SelectedValue, drpMonth.SelectedValue));
            var styletime = hssfworkbook.CreateCellStyle();
            styletime.Alignment = HorizontalAlignment.CENTER;
            styletime.VerticalAlignment = VerticalAlignment.CENTER;
            Font fonttime = hssfworkbook.CreateFont();
            fonttime.FontHeight = 15 * 15;

            styletime.SetFont(fonttime);
            celltime.CellStyle = styletime;
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 6));

            ((HSSFSheet)sheet1).SetEnclosedBorderOfRegion(titlerangeaddress, CellBorderType.DOTTED, NPOI.HSSF.Util.HSSFColor.RED.index);

 

          
            var dt = SqlHelper.ExecuteDataset(Session["Conn"].ToString(),
               "Proc_ProductInOutMonthReport", CurrentDateTime).Tables[0];


            #region"创建头部"
            var rowheader = sheet1.CreateRow(2);
            List<string> headers = new List<string>() { "序号", "材料类别", "期初", "入库", "出库", "月结", "备注" };
            int count=0;
            headers.ForEach(h =>
            {
                rowheader.CreateCell(count).SetCellValue(h);
                count++;
            });
            #endregion


            
            for (int i = 3; i <= dt.Rows.Count+2; i++)
            {
                Row itemrow = sheet1.CreateRow(i);
                DataRow dr = dt.Rows[i - 3];
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var itemcell = itemrow.CreateCell(j);

                    if (dt.Columns[j].DataType == typeof(decimal) || dt.Columns[j].DataType == typeof(float)
                        || dt.Columns[j].DataType == typeof(double))
                    {

                        double value = Math.Round(double.Parse(dr[j].ToString()), 2);
                        itemcell.SetCellType(CellType.NUMERIC);
                        itemcell.SetCellValue(value);
                    }
                    else
                    {
                        object value = dr[j];
                        itemcell.SetCellType(CellType.STRING);
                        itemcell.SetCellValue(value.ToString());
                    }
                }
            }
            string footer = "编制部门：机具物资中心[仓储队]";
            int index = 1 + 1 + 1 + dt.Rows.Count;
            var rowfooter = sheet1.CreateRow(index);
            var stylefooter = hssfworkbook.CreateCellStyle();
            stylefooter.Alignment = HorizontalAlignment.RIGHT;
            stylefooter.VerticalAlignment = VerticalAlignment.CENTER;

            var cellfooter = rowfooter.CreateCell(0);
            cellfooter.CellStyle=(stylefooter);
            
            cellfooter.SetCellValue(footer);
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(index, index, 0, 6));
        }

        MemoryStream WriteToStream()
        {
            //Write the stream data of workbook to the root directory
            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);
            return file;
        }



        void InitializeWorkbook()
        {
            hssfworkbook = new HSSFWorkbook();

            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

            dsi.Company = "广州省输变电工程公司";
            hssfworkbook.DocumentSummaryInformation = dsi;
            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "材料进出存月报表";
            hssfworkbook.SummaryInformation = si;
           

        }

        protected override void OnUnload(EventArgs e)
        {
            if (hssfworkbook != null)
            {
                hssfworkbook.Dispose();
            }
            base.OnUnload(e);
        }
    }
}